"""
Case Type   : rowcount_big()函数
Case Name   : 导入导出
Create At   : 2025/07/31
Owner       : zym_2025
Description :
    1、建表;
    2、插入数据并使用rowcount_big();
    3、更新数据;
    4、删除数据;
    5、gs_dump导出数据;
    6、gs_restore导入到其他D库;
    7、查表并使用rowcount_big();
    8、清理环境;
Expect      :
    1、建表成功;
    2、插入数据成功,返回1;
    3、数据更新成功;
    4、删除数据;
    5、执行成功;
    6、执行成功;
    7、查询成功;
    4、清理环境成功;
History     :
"""

import os
import unittest
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node
from yat.test import macro

class RowcountBig(unittest.TestCase):

    def setUp(self):
        self.log = Logger()
        self.log.info('--OpenGauss_Compatibility_Function_Rowcount_Big_Case0027 start--')
        self.constant = Constant()
        self.node = Node(node='PrimaryDbUser')
        self.comsh = CommonSH('PrimaryDbUser')
        self.db1_name = 'db_d1'
        self.table_name = 't_rowcount_0032'
        self.db2_name = 'db_d2'
        self.dump_file = os.path.join(macro.DB_INSTANCE_PATH, 't_rowcount_0032.dump')

    def testRowcountBig(self):
        text = '------创建D库;expect:成功------'
        self.log.info(text)
        create_db = f'''drop database if exists {self.db1_name};create database {self.db1_name} dbcompatibility 'D';
                    drop database if exists {self.db2_name};create database {self.db2_name} dbcompatibility 'D';'''
        res = self.comsh.execut_db_sql(create_db)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, res, '创建D库失败')
        create_ext = f"create extension shark;"
        res = self.comsh.execut_db_sql(create_ext, dbname=self.db1_name)
        self.assertIn(self.constant.create_extension_success, res, '创建shark插件失败')
        res = self.comsh.execut_db_sql(create_ext, dbname=self.db2_name)
        self.assertIn(self.constant.create_extension_success, res, '创建shark插件失败')

        text = '------step1:建表;expect:成功------'
        self.log.info(text)
        create_tab = f'''drop table if exists {self.table_name};
                     create table {self.table_name}(
                     emp_id INT PRIMARY KEY, emp_name VARCHAR(50), salary NUMERIC(10,2));'''
        res = self.comsh.execut_db_sql(create_tab, dbname=self.db1_name)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, res, '建表失败')

        text = '------step2:插入数据;expect:插入成功,返回1------'
        self.log.info(text)
        insert = f'''
                  DO $$
                  declare
                   v_sql TEXT;
                   v_emp_id INT := 101;
                   v_emp_name VARCHAR(50) := '张三';
                   v_salary NUMERIC := 5000.00;
                   v_row_count bigint;
                  begin
                   v_sql := format('INSERT INTO %I (emp_id, emp_name, salary) VALUES ($1, $2, $3)', '{self.table_name}');
                   EXECUTE v_sql USING v_emp_id, v_emp_name, v_salary;
                   SELECT rowcount_big() INTO v_row_count;
                   RAISE NOTICE 'NOTICE: 数据插入成功，影响行数: %', v_row_count;
                  END $$;'''
        res = self.comsh.execut_db_sql(insert, dbname=self.db1_name)
        self.assertIn('NOTICE: 数据插入成功，影响行数', res, '插入失败')

        text = '------step3:更新数据;expect:成功------'
        self.log.info(text)
        update = f'''
                  declare
                   v_row_count bigint;
                  begin
                   update {self.table_name} set salary = salary * 1.1 where salary < 6000;
                   select rowcount_big() into v_row_count;
                   raise NOTICE 'NOTICE: 薪资调整影响行数: %', v_row_count;
                  end;'''
        res = self.comsh.execut_db_sql(update, dbname=self.db1_name)
        self.assertIn('NOTICE: 薪资调整影响行数', res, '更新失败')

        text = '------step4:删除数据;expect:成功------'
        self.log.info(text)
        delete = f'''
                  declare
                   v_row_count bigint;
                  begin
                   delete from {self.table_name};
                   select rowcount_big() into v_row_count;
                   raise NOTICE 'NOTICE: 删除数据，影响行数: %', v_row_count;
                  end;'''
        res = self.comsh.execut_db_sql(delete, dbname=self.db1_name)
        self.assertIn('NOTICE: 删除数据，影响行数', res, '删除失败')

        text = '------step5:gs_dump导出数据;expect:成功------'
        self.log.info(text)
        cmd = f'source {macro.DB_ENV_PATH}; echo $PGPORT'
        port = self.node.sh(cmd).result()
        cmd = f'''source {macro.DB_ENV_PATH};
                  gs_dump {self.db1_name} -p {port} -f {self.dump_file} -F c -t {self.table_name}'''
        res = self.node.sh(cmd).result()
        self.assertIn(f'dump database {self.db1_name} successfully', res, '数据导出失败')

        text = '------step6:gs_restore导入数据;expect:成功------'
        self.log.info(text)
        cmd = f'''source {macro.DB_ENV_PATH};
                  gs_restore {self.dump_file} -p {port} -d {self.db2_name}'''
        res = self.node.sh(cmd).result()
        self.assertIn('restore operation successful', res, '数据导入失败')

        text = '------step7:查表并执行rowcount_big();expect:查询成功------'
        self.log.info(text)
        select = f'select * from {self.table_name};select rowcount_big();'
        res = self.comsh.execut_db_sql(select, dbname=self.db1_name)
        self.assertIn('0', res.splitlines()[-2], 'rowcount_big()查询异常')

    def tearDown(self):
        text = '------step8:清理环境;expect:成功------'
        self.log.info(text)
        del_sql = f"drop table if exists {self.table_name};"
        res = self.comsh.execut_db_sql(del_sql, dbname=self.db1_name)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, res, '删表失败')

        cmd = f'rm -f {self.dump_file}'
        self.node.sh(cmd)

        drop_sql = f"drop database {self.db1_name};drop database {self.db2_name};"
        res = self.comsh.execut_db_sql(drop_sql)
        self.assertIn(self.constant.DROP_DATABASE_SUCCESS, res.splitlines()[0], '删除D库失败')
        self.assertIn(self.constant.DROP_DATABASE_SUCCESS, res.splitlines()[1], '删除D库失败')
        self.log.info(f'------OpenGauss_Compatibility_Function_Rowcount_Big_Case0032 end------')